package db;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.SQLSyntaxErrorException;
import java.sql.Statement;
import java.util.Date;

import utils.Pair;

public class MySqlHandler {

	public static Connection getConnection(String connectionStr) {
		return getConnection(connectionStr, null, null);
	}

	static String selectStatement(String tblName, String where) {
		String s = "SELECT * FROM " + tblName;
		if (where.length() > 0) {
			s = s + " WHERE " + where;
		}

		return s;
	}

	public static Connection getConnection(String connectionStr, String usr, String passwd) {
		Connection connect = null;
		try {
			Class.forName("com.mysql.jdbc.Driver");
			if (usr == null) {
				connect = DriverManager.getConnection(connectionStr);
			} else {
				connect = DriverManager.getConnection(connectionStr, usr, passwd);
			}
		} catch (Exception e) {
			e.printStackTrace();
		}
		// Setup the connection with the DB
		return connect;
	}

	public static ResultSet query(Connection connect, String str) {
		// Statements allow to issue SQL queries to the database
		Statement statement;
		ResultSet resultSet = null;

		try {
			statement = connect.createStatement();
			resultSet = statement.executeQuery(str);
			writeResultSet(resultSet);
		} catch (Exception e) {
			e.printStackTrace();
		}
		return resultSet;
	}

	public static void update(Connection connect, PreparedStatement PreparedStatement) {
		try {
			PreparedStatement.executeUpdate();
		} catch (Exception e) {
			e.printStackTrace();
		}

	}

	public static ResultSet querySelect(Connection conn, String tblName) {
		return querySelect(conn, tblName, "");
	}

	public static ResultSet querySelect(Connection conn, String tblName, String where) {
		return query(conn, selectStatement(tblName, where));
	}

	public static PreparedStatement getPreparedStatement(Connection conn, String command) {
		try {
			return conn.prepareStatement(command);
		} catch (Exception e) {
			e.printStackTrace();
		}

		return null;
	}

	private void writeMetaData(ResultSet resultSet) throws SQLException {
		// Now get some metadata from the database
		// Result set get the result of the SQL query

		System.out.println("The columns in the table are: ");

		System.out.println("Table: " + resultSet.getMetaData().getTableName(1));
		for (int i = 1; i <= resultSet.getMetaData().getColumnCount(); i++) {
			System.out.println("Column " + i + " " + resultSet.getMetaData().getColumnName(i));
		}
	}

	private static void writeResultSet(ResultSet resultSet) throws SQLException {
		// ResultSet is initially before the first data set
		while (resultSet.next()) {
			// It is possible to get the columns via name
			// also possible to get the columns via the column number
			// which starts at 1
			// e.g. resultSet.getSTring(2);
			String user = resultSet.getString("myuser");
			String website = resultSet.getString("webpage");
			String summery = resultSet.getString("summery");
			Date date = resultSet.getDate("datum");
			String comment = resultSet.getString("comments");
			System.out.println("User: " + user);
			System.out.println("Website: " + website);
			System.out.println("Summery: " + summery);
			System.out.println("Date: " + date);
			System.out.println("Comment: " + comment);
		}
	}

	/*
	 * // You need to close the resultSet private void close() { try { if (resultSet != null) { resultSet.close(); }
	 * 
	 * if (statement != null) { statement.close(); }
	 * 
	 * if (connect != null) { connect.close(); } } catch (Exception e) {
	 * 
	 * 
	 * } }
	 */

	public static String getUpdateCommand(String tblname, int noCols, String idCol, int idVal) {
		String cmd = "UPDATE " + tblname + " SET ";
		for (int i = 0; i < noCols; i++) {
			cmd += " ? = ? ";
			if (i < noCols - 1) {
				cmd = cmd + ", ";
			}
		}

		return cmd + " where " + idCol + " = " + idVal;
	}

	public static String getInsertCommand(String tblname, int noCols) {
		String cmd = "";
		for (int i = 0; i < noCols; i++) {
			cmd += " ? ";
			if (i < noCols - 1) {
				cmd = cmd + ", ";
			}
		}

		return "INSERT INTO " + tblname + " " + cmd + " VALUES ( " + cmd + " ) ";
	}
}